package com.gio.tools.modules.dsp.controller;

import com.gio.tools.common.config.ProjectConfig;
import com.gio.tools.common.utils.DateUtils;
import com.gio.tools.common.utils.FileUtils;
import com.gio.tools.modules.dsp.dao.TableDao;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import java.io.File;
import java.io.IOException;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.*;

@Controller
@RequestMapping(value = "/sd")
public class SynchronizeDataController {

    @Autowired
    private TableDao tableDao;
    @Autowired
    private ProjectConfig projectConfig;

    @RequestMapping(value = "/generate")
    public void generate() {
        // 获取所有表名
        List<String> tableNameList = tableDao.findTableNameList();
        // 遍历所有表
        List<String> sqlStringList = new ArrayList<>();
        for(String tableName : tableNameList) {
//            String tableName = "demand_demand";
            String sql = "INSERT INTO {0} ({1}) values \n";
            // 获取表结构
            List<LinkedHashMap<String, Object>> columnMapList = tableDao.findTableStructure(tableName);
            // 获取字段
            List<String> columnNameList = new ArrayList<>();
            for (Map<String, Object> columnMap : columnMapList) {
                columnNameList.add(String.valueOf(columnMap.get("column_name")));
            }
            // 获取值
            List<LinkedHashMap<String, Object>> valueMapList = tableDao.findData(tableName);
            if(valueMapList != null && !valueMapList.isEmpty()){
                List<List<Object>> valueListList = new ArrayList<>();
                for (Map<String, Object> valueMap : valueMapList) {
                    List<Object> valueList = new ArrayList<>();
                    for (String columnName : columnNameList) {
                        valueList.add(valueMap.get(columnName));
                    }
                    valueListList.add(valueList);
                }
                // 制作values的sql语句
                sql = MessageFormat.format(sql, tableName, StringUtils.join(columnNameList, ","));
                List<String> valuesStringList = new ArrayList<>();
                for (List<Object> valueList : valueListList) {
                    List<String> valueStringList = new ArrayList<>();
                    for (Object value : valueList) {
                        if (value == null) {
                            valueStringList.add("null");
                        } else if (value instanceof Long) {
                            valueStringList.add(String.valueOf(value));
                        } else if (value instanceof Timestamp) {
                            Timestamp timestamp = (Timestamp) value;
                            timestamp.setTime(timestamp.getTime() + 1000 * 60 * 60 * 8);
                            valueStringList.add("'" + timestamp.toString()+ "'");
                        }else {
                            valueStringList.add("'" + value + "'");
                        }
                    }
                    valuesStringList.add("(" + StringUtils.join(valueStringList, ",") + ")");
                }
                sql = sql + StringUtils.join(valuesStringList, ",\n");
                sqlStringList.add(sql);
            }
        }
        // 写入文件
        String filePath = projectConfig.getUploadUrl() + File.separator + DateUtils.getDate("yyyyMMdd") + ".sql";
        System.out.println(filePath);
        FileUtils.createFile(filePath);
        try {
            FileUtils.write(new File(filePath), StringUtils.join(sqlStringList, ";\n"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
